from sqlalchemy import create_engine, Column, Integer, String, text, inspect
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

# 创建数据库引擎
# 请根据实际情况修改数据库连接信息
engine = create_engine('mysql+mysqlconnector://root:123456@127.0.0.1:3306/django-web')
# 创建会话工厂
Session = sessionmaker(bind=engine)
session = Session()

class MySqlSession:
    @staticmethod
    def get_session():
        return session


# 创建基类
Base = declarative_base()


# 定义数据模型
class Article(Base):
    __tablename__ = 'blog_article'

    # 转换成类的属性：title,author,price,content,pub_date,status
    id = Column(Integer, primary_key=True)
    title = Column(String(200))
    author = Column(String(100))
    price = Column(Integer)
    content = Column(String(200))
    pub_date = Column(String(200))
    status = Column(Integer)
    def __repr__(self):
        return f"<Article(title='{self.title}', author='{self.author}', price={self.price})>"



# 创建表
#Base.metadata.create_all(engine)

'''构建article表数据'''
def create_my_articls():
    list=[]
    # 创建article对象
    article = Article(title='Python', author='张三', price=100, content='Python是一种高级编程语言',
                      pub_date='2023-05-01', status=0)
    list.append(article)
    article = Article(title='Java', author='李四', price=200, content='Java是一种面向对象的编程语言',
                      pub_date='2023-05-02', status=1)
    list.append(article)
    article = Article(title='C++', author='王五', price=300, content='C++是一种通用的编程语言', pub_date='2023-05-03',
                      status=0)
    list.append(article)
    article = Article(title='PHP', author='赵六', price=400, content='PHP是一种服务器端脚本语言', pub_date='2023-05-04',
                      status=1)
    list.append(article)
    article = Article(title='JavaScript', author='钱七', price=500, content='JavaScript是一种客户端脚本语言',
                      pub_date='2023-05-05', status=0)
    list.append(article)
    return list



# 插入数据
def add_article():
    list=create_my_articls()
    for article in list:
        print("----插入数据----")
        session.add(article)
        session.commit()
        # 关闭会话
        session.close()


# 查询数据
def query_all_articles():
    articles = session.query(Article).all()
    for article in articles:
        print(article)
    # 关闭会话
    session.close()

# 更新数据
def update_article():
    article_update = session.query(Article).filter_by(title='Python').first()
    if article_update:
        article_update.status = 1
        session.commit()
    # 关闭会话
    session.close()

# 删除数据
def delete_article():
    article_delete = session.query(Article).filter_by(id=1).first()
    if article_delete:
        session.delete(article_delete)
        session.commit()
    # 关闭会话
    session.close()

def table_matadata():

    #session.execute("select * from blog_article")
    # sql="select * from blog_article",查出表数据
    sql="select id,title,status from blog_article where id=:id"
    inspector = inspect(engine)
    # 获取表信息
    columns = inspector.get_columns('blog_article')

    # for col in columns:
    #     # 打印出表字段信息
    #     print(col)
    #     print(f"字段名: {col['name']}, 类型: {col['type']}")
    #改成可以接受动态参数的sql


    result = session.execute(text(sql))
    # 打印出表数据
    list=[]
    for row in result:
        row_dict = row_wrapper(row,columns)
        list.append(row_dict)
    print("--------打印出list=========")
    print(list)
    # 关闭会话
    session.close()
def row_wrapper(row,columns):
    row_dict={}
    index=0
    # 字段驱动数据
    for col in columns:
        col_name = col['name']
        col_type = col['type']
        #取row中的值
        value = row[index]
        index = index + 1
        row_dict[col_name] = value

    print(row_dict)
    return row_dict


import json
from sqlalchemy import create_engine, text
from sqlalchemy.engine import CursorResult


def execute_query_to_json(engine, sql: str, params: dict = None):
    """
    执行 SQL 查询并返回 JSON 格式数据（支持参数化查询）
    :param engine: SQLAlchemy 数据库引擎
    :param sql: SQL 查询语句（可使用:param_name形式参数）
    :param params: 参数字典，如{'id':1,'title':'Python'}
    :return: 字典列表（JSON 兼容格式）
    """
    try:
        with engine.connect() as conn:
            # 执行带参数的查询
            result: CursorResult = conn.execute(text(sql), params or {})

            # 获取列名
            columns = [col[0] for col in result.cursor.description]

            json_data = []
            for row in result:
                row_dict = {}
                for idx, value in enumerate(row):
                    col_name = columns[idx]
                    # 处理日期/时间类型
                    if hasattr(value, 'isoformat'):
                        row_dict[col_name] = value.isoformat()
                    else:
                        row_dict[col_name] = value
                json_data.append(row_dict)
            return json_data
    except Exception as e:
        print(f"查询执行失败: {str(e)}",e)
        return []


def execute_query_to_json_with_page(engine, sql: str, params: dict = None,
                                  page: int = 1, page_size: int = 5):
    """
    执行 SQL 查询并返回 JSON 格式数据（支持分页和参数化查询）
    :param engine: SQLAlchemy 数据库引擎
    :param sql: SQL 查询语句（可使用:param_name形式参数）
    :param params: 参数字典，如{'id':1,'title':'Python'}
    :param page: 页码，从1开始
    :param page_size: 每页条数，默认5条
    :return: {'total':总条数, 'data':字典列表}
    """
    try:
        with engine.connect() as conn:
            # 先查询总条数
            count_sql = f"SELECT COUNT(1) FROM ({sql}) as t"
            total = conn.execute(text(count_sql), params or {}).scalar()

            # 添加分页条件
            paginated_sql = f"""
                SELECT * FROM ({sql}) as t
                LIMIT {page_size} OFFSET {(page-1)*page_size}
            """

            # 执行带参数的查询
            result: CursorResult = conn.execute(text(paginated_sql), params or {})

            # 获取列名
            columns = [col[0] for col in result.cursor.description]

            json_data = []
            for row in result:
                row_dict = {}
                for idx, value in enumerate(row):
                    col_name = columns[idx]
                    # 处理日期/时间类型
                    if hasattr(value, 'isoformat'):
                        row_dict[col_name] = value.isoformat()
                    else:
                        row_dict[col_name] = value
                json_data.append(row_dict)

            return {
                'total': total,
                'data': json_data
            }
    except Exception as e:
        print(f"查询执行失败: {str(e)}", e)
        return {'total': 0, 'data': []}

# 示例用法
if __name__ == "__main__":
    # 创建数据库引擎（以 SQLite 为例）
    #engine = create_engine("sqlite:///mydatabase.db")

    # 示例 SQL（包含联表查询和字段冲突）

   sql = "select id,title,status from blog_article where id=:id"
   sql2=" select * from blog_article where title like :title"
   params = {
        # 'name':1,
        'title':'%P%'
    }

   # 执行查询并获取 JSON 格式数据
   #data = execute_query_to_json(engine,sql2,params)
   result=execute_query_to_json_with_page(engine,sql2,params,2,3)
   print(json.dumps(result, indent=2, ensure_ascii=False))

